﻿<%@ WebHandler Language="C#" Class="ZSMonthTaskBoardV2" %>

using System;
using System.Web;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using Newtonsoft.Json.Converters;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Collections;
using System.IO;
using ReportBusiness;

/**
 * Description: 月度装配试车计划V2
 * Copyright (c): 通力凯顿（北京）系统集成有限公司
 * Author: zhunan
 * Date: 2023.4.11
 */

public class ZSMonthTaskBoardV2 : IHttpHandler
{
    uMESReportOracleDAL dal = new uMESReportOracleDAL();
    public bool IsReusable { get { return false; } }
    public void ProcessRequest(HttpContext context)
    {
        string requestjson = "";
        using (StreamReader reader = new StreamReader(context.Request.InputStream)) { requestjson = reader.ReadToEnd(); }
        JObject jObject = JsonConvert.DeserializeObject(requestjson) as JObject;
        if (jObject["type"] != null)
        {
            try
            {
                Result r = new Result();
                context.Response.ContentType = "text/json";
                string type = jObject["type"].ToString();
                switch (type)
                {
                    case "getData":
                        r = getData(jObject);//查询
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getEngineCatchInfoData":
                        r = getEngineCatchInfoData(jObject);//发动机追赶及现状信息
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "saveEngineCatchData":
                        r = saveEngineCatchData(jObject);//保存追赶及现状信息
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getLackInfo":
                        r = getLackInfo(jObject);//缺件信息
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getDealFaultInfo":
                        r = getDealFaultInfo(jObject);//返件信息
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getContainerInfo":
                        r = getContainerInfo(jObject);//任务单信息
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getContainerSpecInfo":
                         r = getContainerSpecInfo(jObject);//任务单工序明细
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    default:
                        break;
                }
            }
            catch (Exception e)
            {
                Result r = new Result();
                r.data = null;
                r.meta = new Result.Meta(500, e.Message);
                r.total = 0;
                context.Response.ContentType = "text/json";
                context.Response.Write(JsonConvert.SerializeObject(r));
            }
        }
    }

    Result getData(JObject jObject)
    {
        string sql = @"
SELECT t.engineno,
       t.workflowstepname lastWorkflowstepname,--工作内容
       to_char(t.plannedfinishdate,'mm') || '月'  || to_char(t.plannedfinishdate,'dd') || '日' lastPlanDate,--计划节点
       t2.planNeedHours,--计划总耗时
       t2.actualUsedHours,--实际总耗时
       t2.diffHours --总差额
FROM (
  SELECT u.attributevalue engineno,ws.workflowstepname,ep.plannedfinishdate,
         '第'||row_number() OVER(PARTITION BY u.attributevalue ORDER BY ep.plannedfinishdate DESC) maxseq 
  FROM mfg_engine_data t
  LEFT JOIN mfgorder mfg ON mfg.mfgorderid = t.mfgorderid
  LEFT JOIN userattribute u ON u.parentid = mfg.mfgorderid AND u.userattributename = '发动机' 
  LEFT JOIN workflowstep ws ON ws.workflowid = t.engineworkflowid
  LEFT JOIN enginepartplan ep ON ep.mfgorderid = t.mfgorderid AND ep.workflowstepid = ws.workflowstepid AND ep.containerno IS NULL
  LEFT JOIN orderstatus od ON od.orderstatusid = mfg.orderstatusid
  WHERE NVL(od.orderstatusname,'打开') <> '关闭'
        AND ep.plannedfinishdate >= TO_DATE('{0} 00:00:00','yyyy-mm-dd hh24:mi:ss') AND ep.plannedfinishdate <= TO_DATE('{1} 23:59:59','yyyy-mm-dd hh24:mi:ss') AND {2}
) t
LEFT JOIN 
(
  SELECT tt.engineno,SUM(tt.planNeedHours) planNeedHours,SUM(tt.actualUsedHours) actualUsedHours,SUM(tt.diffHours) diffHours
  FROM (
    SELECT u.attributevalue engineno,ws.workflowstepname,
           ROUND(ep.plannedfinishdate - ep.plannedstartdate,2) * 24 planNeedHours,
           CASE WHEN ep.finishdate IS NOT NULL AND csd.startworkdate IS NOT NULL THEN ROUND((ep.finishdate - csd.startworkdate) * 24,2) 
                ELSE 0 
           END actualUsedHours,
           CASE WHEN ep.finishdate IS NOT NULL AND csd.startworkdate IS NOT NULL
                THEN ROUND(((ep.finishdate - csd.startworkdate) - (ep.plannedfinishdate - ep.plannedstartdate)) * 24,2) ELSE 0
           END diffHours
    FROM mfg_engine_data t
    LEFT JOIN mfgorder mfg ON mfg.mfgorderid = t.mfgorderid
    LEFT JOIN userattribute u ON u.parentid = mfg.mfgorderid AND u.userattributename = '发动机' 
    LEFT JOIN workflowstep ws ON ws.workflowid = t.engineworkflowid
    LEFT JOIN enginepartplan ep ON ep.mfgorderid = t.mfgorderid AND ep.workflowstepid = ws.workflowstepid AND ep.containerno IS NULL
    LEFT JOIN (
      SELECT u.attributevalue engineno,ep.workflowstepname,csd.startworkdate,row_number() OVER(PARTITION BY ep.machineno,ep.workflowstepname  ORDER BY csd.startworkdate) seq 
      FROM containerstepdispatch csd
      LEFT JOIN enginepartplan ep ON ep.containerid = csd.containerid
      LEFT JOIN mfgorder mfg ON mfg.mfgorderid = ep.mfgorderid
      LEFT JOIN userattribute u ON u.parentid = mfg.mfgorderid AND u.userattributename = '发动机' 
      WHERE csd.factoryid = '{3}' AND u.attributevalue IS NOT NULL AND csd.startworkdate IS NOT NULL AND csd.createdate >= TO_DATE('{0} 00:00:00','yyyy-mm-dd hh24:mi:ss') AND {2}
    ) csd ON csd.engineno = u.attributevalue AND csd.workflowstepname = ws.workflowstepname AND seq = 1
    WHERE ep.plannedfinishdate >= TO_DATE('{0} 00:00:00','yyyy-mm-dd hh24:mi:ss') AND ep.plannedfinishdate <= TO_DATE('{1} 23:59:59','yyyy-mm-dd hh24:mi:ss') AND {2}
  ) tt
  GROUP BY tt.engineno
) t2 ON t2.engineno = t.engineno
WHERE t.maxseq = '第1'";

        //发动机号
        if (jObject["engineno"] != null && jObject["engineno"].ToString() != "")
        {
            JArray selectdate = JArray.Parse(jObject["selectdate"].ToString());
            string startdate = selectdate[0].ToString();
            string enddate = selectdate[1].ToString();
            string engineno = jObject["engineno"].ToString();
            string factoryid = jObject["factoryid"].ToString();
            sql = string.Format(sql, startdate, enddate, " u.attributevalue = '" + engineno + "'", factoryid);
        }
        else
        {
            JArray selectdate = JArray.Parse(jObject["selectdate"].ToString());
            string startdate = selectdate[0].ToString();
            string enddate = selectdate[1].ToString();
            string factoryid = jObject["factoryid"].ToString();
            sql = string.Format(sql, startdate, enddate, " 1=1 ", factoryid);
        }

        uMESPagingDataDTO dto = dal.GetPagingDataIns(sql, (int)jObject["currentPage"], (int)jObject["pageSize"]);
        DataTable dtResult = dto.DBTable;

        JArray colums = JArray.Parse(jObject["colums"].ToString());
        for (int i = 0; i < colums.Count; i++)
        {
            dtResult.Columns.Add(colums[i].ToString(), typeof(object));
        }

        sql = @"
SELECT t.*,to_char(to_date(REGEXP_SUBSTR(t.newdate, '[^,]+', 1,L),'yyyy-mm-dd'),'yyyy-mm-dd') selectdate,L
FROM (
    SELECT u.attributevalue engineno,ws.workflowstepname,
           ep.plannedstartdate,ep.plannedfinishdate,
           '计划' datatype,
           mesreport_getskip(ep.plannedstartdate,ep.plannedfinishdate) newdate,
           csd.startworkdate,ep.finishdate,
           CASE WHEN ep.usecompleted = 0 THEN '未完工'
                WHEN ep.usecompleted = 1 AND trunc(ep.finishdate) > TRUNC(ep.plannedfinishdate) THEN '延期完工'
                ELSE '按时完工'
           END finishStatus
    FROM mfg_engine_data t
    LEFT JOIN mfgorder mfg ON mfg.mfgorderid = t.mfgorderid
    LEFT JOIN userattribute u ON u.parentid = mfg.mfgorderid AND u.userattributename = '发动机' 
    LEFT JOIN workflowstep ws ON ws.workflowid = t.engineworkflowid
    LEFT JOIN enginepartplan ep ON ep.mfgorderid = t.mfgorderid AND ep.workflowstepid = ws.workflowstepid AND ep.containerno IS NULL
    LEFT JOIN
    (
      SELECT u.attributevalue engineno,ep.workflowstepname,csd.startworkdate,row_number() OVER(PARTITION BY ep.machineno,ep.workflowstepname  ORDER BY csd.startworkdate) seq 
      FROM containerstepdispatch csd
      LEFT JOIN enginepartplan ep ON ep.containerid = csd.containerid
      LEFT JOIN mfgorder mfg ON mfg.mfgorderid = ep.mfgorderid
      LEFT JOIN userattribute u ON u.parentid = mfg.mfgorderid AND u.userattributename = '发动机' 
      WHERE csd.factoryid = '{3}' AND u.attributevalue IS NOT NULL AND csd.startworkdate IS NOT NULL 
            AND csd.createdate >= TO_DATE('{0} 00:00:00','yyyy-mm-dd hh24:mi:ss') AND {2}
    ) csd ON csd.engineno = u.attributevalue AND csd.workflowstepname = ws.workflowstepname AND seq = 1
    WHERE ep.plannedfinishdate >= TO_DATE('{0} 00:00:00','yyyy-mm-dd hh24:mi:ss') AND ep.plannedfinishdate <= TO_DATE('{1} 23:59:59','yyyy-mm-dd hh24:mi:ss') AND {2}
) t,
(SELECT LEVEL L FROM dual CONNECT BY LEVEL <= 100 ) --限制一下level数量，提高查询速度。否则数据较多的情况下，会一直查询不出结果
WHERE L ( + ) <= regexp_count(t.newdate,',') + 1 -- 计算需要分割后的数量";

        //发动机号
        if (jObject["engineno"] != null && jObject["engineno"].ToString() != "")
        {
            JArray selectdate = JArray.Parse(jObject["selectdate"].ToString());
            string startdate = selectdate[0].ToString();
            string enddate = selectdate[1].ToString();
            string engineno = jObject["engineno"].ToString();
            string factoryid = jObject["factoryid"].ToString();
            sql = string.Format(sql, startdate, enddate, " u.attributevalue = '" + engineno + "'", factoryid);
        }
        else
        {
            JArray selectdate = JArray.Parse(jObject["selectdate"].ToString());
            string startdate = selectdate[0].ToString();
            string enddate = selectdate[1].ToString();
            string factoryid = jObject["factoryid"].ToString();
            sql = string.Format(sql, startdate, enddate, " 1=1 ", factoryid);
        }

        DataTable dt = dal.GetDataTable(sql);
        foreach (DataRow dr in dtResult.Rows)
        {
            for (int i = 7; i < dtResult.Columns.Count; i++)
            {
                string filter = string.Format("engineno = '{0}' AND selectdate='{1}'", dr["engineno"], dtResult.Columns[i].ColumnName);
                DataRow[] drList = dt.Select(filter);
                DataTable dtPlanSpec;
                if (drList.Length > 0)
                {
                    dtPlanSpec = drList.CopyToDataTable();
                    foreach (DataColumn col in dtPlanSpec.Columns)
                    {
                        col.ColumnName = col.ColumnName.ToLower();
                    }
                    dr[dtResult.Columns[i].ColumnName] = dtPlanSpec;
                }
                else
                {
                    dr[dtResult.Columns[i].ColumnName] = new DataTable();
                }
            }
        }

        foreach (DataColumn col in dtResult.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dtResult;
        r.total = Convert.ToInt32(dto.RowCount);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //发动机追赶及调整信息
    //    Result getEngineCatchInfoData(JObject jObject)
    //    {
    //        JArray selectdate = JArray.Parse(jObject["selectdate"].ToString());
    //        string startdate = selectdate[0].ToString();
    //        string enddate = selectdate[1].ToString();
    //        string engineno = jObject["engineno"].ToString();

    //        DataTable dtResult = new DataTable();
    //        dtResult.Columns.Add("engineno", typeof(string));
    //        DataRow dr = dtResult.NewRow();
    //        dr["engineno"] = engineno;
    //        dtResult.Rows.Add(dr);

    //        string sql = @"
    //SELECT ws.workflowstepname
    //FROM mfg_engine_data t
    //LEFT JOIN mfgorder mfg ON mfg.mfgorderid = t.mfgorderid
    //LEFT JOIN userattribute u ON u.parentid = mfg.mfgorderid AND u.userattributename = '发动机' 
    //LEFT JOIN workflowstep ws ON ws.workflowid = t.engineworkflowid
    //WHERE u.attributevalue = '{0}'
    //ORDER BY u.attributevalue,ws.workflowstepname";
    //        sql = string.Format(sql, engineno);
    //        DataTable dtBigStep = dal.GetDataTable(sql);

    //        foreach (DataRow drStep in dtBigStep.Rows)
    //        {
    //            dtResult.Columns.Add(drStep["workflowstepname"].ToString(), typeof(object));
    //        }

    //        sql = @"
    //SELECT u.attributevalue engineno,ws.workflowstepname,ws.workflowstepid,
    //       ep.plannedstartdate,ep.plannedfinishdate,ep.finishdate,ad.currentsituationdate,ad.catchdate
    //FROM mfg_engine_data t
    //LEFT JOIN mfgorder mfg ON mfg.mfgorderid = t.mfgorderid
    //LEFT JOIN userattribute u ON u.parentid = mfg.mfgorderid AND u.userattributename = '发动机' 
    //LEFT JOIN workflowstep ws ON ws.workflowid = t.engineworkflowid
    //LEFT JOIN enginepartplan ep ON ep.mfgorderid = t.mfgorderid AND ep.workflowstepid = ws.workflowstepid AND ep.containerno IS NULL
    //LEFT JOIN zsenginebigstepplanadjust ad ON ad.engineno = u.attributevalue AND ad.workflowstepname = ws.workflowstepname
    //WHERE u.attributevalue = '{0}'
    //ORDER BY u.attributevalue,ws.workflowstepname";
    //        sql = string.Format(sql, engineno);
    //        DataTable dt = dal.GetDataTable(sql);

    //        foreach (DataRow datarow in dtResult.Rows)
    //        {
    //            for (int i = 1; i < dtResult.Columns.Count; i++)
    //            {
    //                string filter = string.Format("engineno = '{0}' AND workflowstepname='{1}'", dr["engineno"], dtResult.Columns[i].ColumnName);
    //                DataRow[] drList = dt.Select(filter);
    //                DataTable dtPlanSpec;
    //                if (drList.Length > 0)
    //                {
    //                    dtPlanSpec = drList.CopyToDataTable();
    //                    foreach (DataColumn col in dtPlanSpec.Columns)
    //                    {
    //                        col.ColumnName = col.ColumnName.ToLower();
    //                    }
    //                    dr[dtResult.Columns[i].ColumnName] = dtPlanSpec;
    //                }
    //                else
    //                {
    //                    dr[dtResult.Columns[i].ColumnName] = new DataTable();
    //                }
    //            }
    //        }

    //        foreach (DataColumn col in dtResult.Columns)
    //        {
    //            col.ColumnName = col.ColumnName.ToLower();
    //        }
    //        Result r = new Result();
    //        r.data = dtResult;
    //        r.total = Convert.ToInt32(dtResult.Rows.Count);
    //        r.meta = new Result.Meta(200, "请求成功");
    //        return r;
    //    }


    Result getEngineCatchInfoData(JObject jObject)
    {
        JArray selectdate = JArray.Parse(jObject["selectdate"].ToString());
        string startdate = selectdate[0].ToString();
        string enddate = selectdate[1].ToString();
        string engineno = jObject["engineno"].ToString();

        DataTable dtResult = new DataTable();
        dtResult.Columns.Add("engineno", typeof(string));
        dtResult.Columns.Add("name", typeof(string));
        DataRow dr1 = dtResult.NewRow();
        dr1["name"] = "计划";
        dr1["engineno"] = engineno;
        dtResult.Rows.Add(dr1);

        DataRow dr2 = dtResult.NewRow();
        dr2["name"] = "现状";
        dr2["engineno"] = engineno;
        dtResult.Rows.Add(dr2);

        DataRow dr3 = dtResult.NewRow();
        dr3["name"] = "追赶";
        dr3["engineno"] = engineno;
        dtResult.Rows.Add(dr3);

        DataRow dr4 = dtResult.NewRow();
        dr4["name"] = "实际";
        dr4["engineno"] = engineno;
        dtResult.Rows.Add(dr4);

        string sql = @"
SELECT ws.workflowstepname
FROM mfg_engine_data t
LEFT JOIN mfgorder mfg ON mfg.mfgorderid = t.mfgorderid
LEFT JOIN userattribute u ON u.parentid = mfg.mfgorderid AND u.userattributename = '发动机' 
LEFT JOIN workflowstep ws ON ws.workflowid = t.engineworkflowid
WHERE u.attributevalue = '{0}'
ORDER BY u.attributevalue,ws.workflowstepname";
        sql = string.Format(sql, engineno);
        DataTable dtBigStep = dal.GetDataTable(sql);

        foreach (DataRow drStep in dtBigStep.Rows)
        {
            dtResult.Columns.Add(drStep["workflowstepname"].ToString(), typeof(DateTime));
        }

        sql = @"
SELECT u.attributevalue engineno,ws.workflowstepname,ws.workflowstepid,
       ep.plannedstartdate,ep.plannedfinishdate,ep.finishdate,ad.currentsituationdate,ad.catchdate
FROM mfg_engine_data t
LEFT JOIN mfgorder mfg ON mfg.mfgorderid = t.mfgorderid
LEFT JOIN userattribute u ON u.parentid = mfg.mfgorderid AND u.userattributename = '发动机' 
LEFT JOIN workflowstep ws ON ws.workflowid = t.engineworkflowid
LEFT JOIN enginepartplan ep ON ep.mfgorderid = t.mfgorderid AND ep.workflowstepid = ws.workflowstepid AND ep.containerno IS NULL
LEFT JOIN zsenginebigstepplanadjust ad ON ad.engineno = u.attributevalue AND ad.workflowstepname = ws.workflowstepname
WHERE u.attributevalue = '{0}'
ORDER BY u.attributevalue,ws.workflowstepname";
        sql = string.Format(sql, engineno);
        DataTable dt = dal.GetDataTable(sql);

        foreach (DataRow dr in dtResult.Rows)
        {
            string name = dr["name"].ToString();
            for (int i = 2; i < dtResult.Columns.Count; i++)
            {
                string columnName = dtResult.Columns[i].ColumnName;
                string filter = string.Format("workflowstepname='{0}'", columnName);
                DataRow[] drList = dt.Select(filter);
                if (drList.Length > 0)
                {
                    switch (name)
                    {
                        case "计划":
                            dr[columnName] = drList[0]["plannedfinishdate"];
                            break;
                        case "现状":
                            dr[columnName] = drList[0]["currentsituationdate"];
                            break;
                        case "追赶":
                            dr[columnName] = drList[0]["catchdate"];
                            break;
                        case "实际":
                            dr[columnName] = drList[0]["finishdate"];
                            break;
                        default:
                            break;
                    }

                }
                else
                {
                    dr[columnName] = null;
                }
            }
        }

        foreach (DataColumn col in dtResult.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }

        Result r = new Result();
        r.data = dtResult;
        r.total = Convert.ToInt32(dtResult.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //保存追赶及现状信息
    Result saveEngineCatchData(JObject jObject)
    {
        var dtWillBeSavedData = JsonConvert.DeserializeObject<DataTable>(jObject["willBeSavedData"].ToString());
        string employeeid = jObject["employeeid"].ToString();
        foreach (DataRow dr in dtWillBeSavedData.Rows)
        {
            string name = dr["name"].ToString();
            string engineno = dr["engineno"].ToString();//发动机号
            if (name == "现状")
            {
                foreach (DataColumn col in dtWillBeSavedData.Columns)
                {
                    if (col.ColumnName == "engineno" || col.ColumnName == "name")
                    {
                        continue;
                    }
                    else
                    {
                        string workflowstepname = col.ColumnName;//大阶段名称
                        string currentsituationdate = dr[col.ColumnName].ToString() == "" ? "" : dr[col.ColumnName].ToString().Split(' ')[0];//现状时间
                        string sql = "SELECT * FROM zsenginebigstepplanadjust t WHERE t.engineno = '{0}' AND t.workflowstepname = '{1}'";
                        sql = string.Format(sql, engineno, workflowstepname);
                        DataTable dt = dal.GetDataTable(sql);
                        if (dt.Rows.Count == 0)
                        {
                            string id = Guid.NewGuid().ToString();//唯一id
                            sql = @"
INSERT INTO zsenginebigstepplanadjust(id,engineno,workflowstepname,currentsituationdate,createdate,createemployee)
VALUES('{0}','{1}','{2}',to_date('{3}','yyyy/mm/dd'),SYSDATE,'{4}')";
                            sql = string.Format(sql, id, engineno, workflowstepname, currentsituationdate, employeeid);
                            //执行插入
                            dal.ExecuteSql(sql);
                        }
                        else
                        {
                            sql = @"
UPDATE zsenginebigstepplanadjust t 
SET t.currentsituationdate = to_date('{2}','yyyy/mm/dd'),t.lastchangedate = SYSDATE,t.lastchangeemployee='{3}'
WHERE t.engineno = '{0}' AND t.workflowstepname = '{1}'";
                            sql = string.Format(sql, engineno, workflowstepname, currentsituationdate, employeeid);
                            //执行更新
                            dal.ExecuteSql(sql);
                        }

                    }
                }
            }
            else if (name == "追赶")
            {
                foreach (DataColumn col in dtWillBeSavedData.Columns)
                {
                    if (col.ColumnName == "engineno" || col.ColumnName == "name")
                    {
                        continue;
                    }
                    else
                    {
                        string workflowstepname = col.ColumnName;//大阶段名称
                        string catchdate = dr[col.ColumnName].ToString() == "" ? "" : dr[col.ColumnName].ToString().Split(' ')[0];//追赶时间
                        string sql = "SELECT * FROM zsenginebigstepplanadjust t WHERE t.engineno = '{0}' AND t.workflowstepname = '{1}'";
                        sql = string.Format(sql, engineno, workflowstepname);
                        DataTable dt = dal.GetDataTable(sql);
                        if (dt.Rows.Count == 0)
                        {
                            string id = Guid.NewGuid().ToString();//唯一id
                            sql = @"
INSERT INTO zsenginebigstepplanadjust(id,engineno,workflowstepname,catchdate,createdate,createemployee)
VALUES('{0}','{1}','{2}',to_date('{3}','yyyy/mm/dd'),SYSDATE,'{4}')";
                            sql = string.Format(sql, id, engineno, workflowstepname, catchdate, employeeid);
                            //执行插入
                            dal.ExecuteSql(sql);
                        }
                        else
                        {
                            sql = @"
UPDATE zsenginebigstepplanadjust t 
SET t.catchdate = to_date('{2}','yyyy/mm/dd'),t.lastchangedate = SYSDATE,t.lastchangeemployee='{3}'
WHERE t.engineno = '{0}' AND t.workflowstepname = '{1}'";
                            sql = string.Format(sql, engineno, workflowstepname, catchdate, employeeid);
                            //执行更新
                            dal.ExecuteSql(sql);
                        }
                    }
                }
            }
        }

        Result r = new Result();
        r.data = null;
        r.total = Convert.ToInt32(dtWillBeSavedData.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //缺件信息
    Result getLackInfo(JObject jObject)
    {
        string sql = @"
SELECT ep.machineno,ep.mfgordername,ep.workflowstepname,im.containerno,'正常' containertype,ep.productdesc,sp.specno ,s.specname,
       ep.plannedstartdate,ep.plannedfinishdate,cm.productname,p.description,
       pm.firstassembleqty,pm.checkassembleqty,
       cm.productbatchno,DECODE(pm.parttype,0,'大件','小件') parttype,NVL(con.confirmqty,0) confirmqty,
       NVL(pm.firstassembleqty,0)-NVL(con.confirmqty,0) firstlackqty,
       NVL(pm.checkassembleqty,0)-NVL(con.confirmqty,0) checklackqty,
       {1} checktype
FROM issueerpmaterial im                
LEFT JOIN collecterpmaterial cm ON im.collecterpmaterialid=cm.collecterpmaterialid
LEFT JOIN partmenuinfo pm ON im.partmenuinfoid=pm.partmenuinfoid               
LEFT JOIN product p ON p.productid=pm.productid
LEFT JOIN enginepartplan ep ON ep.containerid =im.containerid
LEFT JOIN spec sp ON sp.specid = pm.specid 
LEFT JOIN specbase s ON s.specbaseid =sp.specbaseid 
LEFT JOIN (
     SELECT SUM(confirmqty)confirmqty,issueerpmaterialid 
     FROM issueerpmaterialconfirmation WHERE DECODE(note,NULL,'1',note)='{1}' --1初装、2简装
     GROUP BY issueerpmaterialid
     )  con ON con.issueerpmaterialid=im.issueerpmaterialid
WHERE ep.machineno = '{0}' AND im.ischange='0' ";
        string engineno = jObject["engineno"].ToString();//发动机号
        string workflowstepname = jObject["workflowstepname"].ToString();//大阶段
        string checktype = workflowstepname == "0010-去封/集件" ? "1" : "2";
        sql = string.Format(sql, engineno, checktype);

        if (checktype == "1")
        {
            sql += " AND NVL(pm.firstassembleqty,0) > NVL(con.confirmqty,0)";
        }
        else
        {
            sql += " AND NVL(pm.checkassembleqty,0) > NVL(con.confirmqty,0)";
        }

        sql += " ORDER BY cm.productname";
        uMESPagingDataDTO dto = dal.GetPagingDataIns(sql, (int)jObject["currentPage"], (int)jObject["pageSize"]);
        DataTable dt = dto.DBTable;
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dto.RowCount);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //返件信息
    Result getDealFaultInfo(JObject jObject)
    {
        string sql = @"
SELECT  d.engineno,mfg.mfgordername,ot.ordertypename,d.enginefamily,
        c.togetherflag,pb.productname,p.description,c.containername, 
        DECODE(d.transferstatus,'0','待审核', '1', '待确认','2', '未返出', '3', '未接收', '4', '未完成','5', '未返回','6', '未领取','7', '已领取', d.transferstatus) transferstatus,
        d.repaircount,d.dealfaultbillname,d.dealfaultbillid,tr.pagecount, 
        getfactoryname_fun(p.factoryid) sendfactory,
        d.transferstatus status,
        getempfullname(tr.fromempid) fromemp,
        tr.fromtime,tr.requirefinishtime,
        getfactoryname_fun(tr.recvfactoryid) recvfactory,
        tr.recvfactoryid,
        t1.teamname recvfactoryteam,
        getempfullname(tr.recvfactoryempid) recvemp,
        tr.recvfactoryqty,tr.recvfactorytime,tr.recvfactoryteamid,
        tr.recvfactorypassqty,tr.recvfactoryfailqty,
        getempfullname(tr.recvfactoryreturnempid) factoryreturnemp,
        getempfullname(tr.acceptempid) acceptemp,
        tr.recvfactoryreturntime,t2.teamname acceptteam,
        getempfullname(tr.dispatchacceptempid) recvfactoryreturnemp,
        tr.accepttime,
        d.machineworkhours,d.faultdesc,d.dealfaulttype,
        getfactoryname_fun(d.returnfactoryid) returnfactory,cno.zbhgdh,tr.fromqty,
        getempfullname(cno.checkemployeeid) checker,d.returnfactoryid,cem.productbatchno comproductname,tr.acceptteamid
FROM dealfaultbill d
LEFT JOIN dealfaultbilltransfer tr ON tr.dealfaultbillid = d.dealfaultbillid
LEFT JOIN container c ON d.containerid = c.containerid
LEFT JOIN product p ON c.productid = p.productid
LEFT JOIN productbase pb ON p.productbaseid=pb.productbaseid
LEFT JOIN mfgorder mfg ON c.mfgorderid = mfg.mfgorderid
LEFT JOIN ordertype ot ON mfg.ordertypeid = ot.ordertypeid
LEFT JOIN team t1 ON t1.teamid=tr.recvfactoryteamid
LEFT JOIN team t2 ON t2.teamid=tr.acceptteamid
LEFT JOIN containernonconforminginfo cno ON cno.containernonconforminginfoid=d.containernonconforminginfoid
LEFT JOIN 
(
  SELECT dd.dealfaultbillid,MAX(dd.issueerpmaterialid) issueerpmaterialid 
  FROM  dealfaultbilldetail dd 
  GROUP BY dd.dealfaultbillid
) dd
ON dd.dealfaultbillid=d.dealfaultbillid
LEFT JOIN issueerpmaterial iem ON iem.issueerpmaterialid = NVL(d.issueerpmaterialid,dd.issueerpmaterialid)
LEFT JOIN collecterpmaterial cem ON cem.collecterpmaterialid=iem.collecterpmaterialid
WHERE d.isuse='0' AND d.engineno = '{0}'
ORDER BY d.dealfaultbillid  ASC
";
        sql = string.Format(sql, jObject["engineno"].ToString());
        DataTable dt = dal.GetDataTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //任务单信息
    Result getContainerInfo(JObject jObject)
    {
        string sql = @"
SELECT NVL(ep.enginecontainerid,ep.containerid) containerid,ep.machineno,ep.mfgordername,ep.containerno,'正常' containertype,ep.productname,ep.productdesc,ep.workflowname,ep.workflowdesc,ep.workflowver,
       ep.plannedstartdate,ep.plannedfinishdate,csd.startworkdate,ep.finishdate
FROM enginepartplan ep
LEFT JOIN (
     SELECT ep.machineno,ep.workflowstepname,MIN(csd.startworkdate) startworkdate
     FROM containerstepdispatch csd
     LEFT JOIN containercurrentinfo cci ON cci.containerid = csd.containerid
     LEFT JOIN enginepartplan ep ON ep.containerid = cci.containerid
     LEFT JOIN mfgorder mfg ON mfg.mfgorderid = ep.mfgorderid
     LEFT JOIN userattribute u ON u.parentid = mfg.mfgorderid AND u.userattributename = '发动机'
     WHERE ep.machineno = '{0}' AND csd.isuse = 0 AND ep.workflowstepname = '{1}' 
     GROUP BY ep.machineno,ep.workflowstepname
) csd ON csd.machineno = ep.machineno AND csd.workflowstepname = ep.workflowstepname
WHERE ep.machineno = '{0}' AND ep.workflowstepname = '{1}'
";
        sql = string.Format(sql, jObject["engineno"].ToString(), jObject["workflowstepname"].ToString());
        DataTable dt = dal.GetDataTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //任务单工序明细
    Result getContainerSpecInfo(JObject jObject)
    {
        string sql = @"
SELECT cci.containerid,cci.workflowid,ep.mfgordername,ep.machineno engineno,ep.workflowstepname bigstep,
       cci.containerid,cci.containername,cci.containerno,cci.productname,cci.productdescription,cci.workflowstepname currentspec,
       ws.workflowstepname,ws.sequence,
       csd.createdate dispatchdate,
       csd.recvdate,csd.startworkdate,
       csw.createdate reportdate,
       cki.createdate checkdate,
       CASE WHEN cki.createdate IS NOT NULL THEN '已完工' 
            WHEN cki.createdate IS NULL AND csw.createdate IS NOT NULL THEN '待检验'        
            WHEN csd.createdate IS NOT NULL AND csd.recvdate IS NOT NULL AND csd.startworkdate IS NOT NULL AND csw.createdate IS NULL THEN '待报工'        
            WHEN csd.createdate IS NOT NULL AND csd.recvdate IS NOT NULL AND csd.startworkdate IS NULL THEN '待开工'        
            WHEN csd.createdate IS NOT NULL AND csd.recvdate IS NULL THEN '待接收'        
            WHEN csd.createdate IS NULL THEN '待派工'
       END status
FROM containercurrentinfo cci
LEFT JOIN workflowstep ws ON ws.workflowid = cci.workflowid
LEFT JOIN (
     SELECT ep.machineno,cci.containername,ws.workflowstepname,csd.createdate,csd.recvdate,csd.startworkdate,
            row_number() OVER(PARTITION BY ep.machineno,cci.containername,ws.workflowstepname ORDER BY csd.createdate DESC) maxseq
     FROM containerstepdispatch csd
     LEFT JOIN containercurrentinfo cci ON cci.containerid = csd.containerid
     LEFT JOIN workflowstep ws ON ws.workflowstepid = csd.workflowstepid
     LEFT JOIN enginepartplan ep ON ep.containerid = cci.containerid
     LEFT JOIN mfgorder mfg ON mfg.mfgorderid = ep.mfgorderid
     LEFT JOIN userattribute u ON u.parentid = mfg.mfgorderid AND u.userattributename = '发动机'
     WHERE csd.containerid = '{0}'
) csd ON csd.containername = cci.containername AND csd.workflowstepname = ws.workflowstepname AND csd.maxseq = 1
LEFT JOIN (
     SELECT ep.machineno,cci.containername,ws.workflowstepname,csw.createdate,
            row_number() OVER(PARTITION BY ep.machineno,cci.containername,ws.workflowstepname ORDER BY csw.createdate DESC) maxseq
     FROM containerstepworkreport csw
     LEFT JOIN containercurrentinfo cci ON cci.containerid = csw.containerid
     LEFT JOIN workflowstep ws ON ws.workflowstepid = csw.workflowstepid
     LEFT JOIN enginepartplan ep ON ep.containerid = cci.containerid
     LEFT JOIN mfgorder mfg ON mfg.mfgorderid = ep.mfgorderid
     LEFT JOIN userattribute u ON u.parentid = mfg.mfgorderid AND u.userattributename = '发动机'
     WHERE csw.containerid = '{0}'
) csw ON csw.containername = cci.containername AND csw.workflowstepname = ws.workflowstepname AND csw.maxseq = 1
LEFT JOIN (
     SELECT ep.machineno,cci.containername,ws.workflowstepname,cki.createdate,
            row_number() OVER(PARTITION BY ep.machineno,cci.containername,ws.workflowstepname ORDER BY cki.createdate DESC) maxseq
     FROM containercheckinfo cki
     LEFT JOIN containercurrentinfo cci ON cci.containerid = cki.containerid
     LEFT JOIN workflowstep ws ON ws.workflowstepid = cki.workflowstepid
     LEFT JOIN enginepartplan ep ON ep.containerid = cci.containerid
     LEFT JOIN mfgorder mfg ON mfg.mfgorderid = ep.mfgorderid
     LEFT JOIN userattribute u ON u.parentid = mfg.mfgorderid AND u.userattributename = '发动机'
     WHERE cki.containerid = '{0}'
) cki ON cki.containername = cci.containername AND cki.workflowstepname = ws.workflowstepname AND cki.maxseq = 1
LEFT JOIN enginepartplan ep ON NVL(ep.enginecontainerid,ep.containerid) = cci.containerid
LEFT JOIN mfgorder mfg ON mfg.mfgorderid = ep.mfgorderid
LEFT JOIN userattribute u ON u.parentid = mfg.mfgorderid AND u.userattributename = '发动机'
WHERE cci.containerid = '{0}'";
        sql = string.Format(sql, jObject["containerid"].ToString(), jObject["containerid"].ToString());
        DataTable dt = dal.GetDataTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }
}